package com.aoyebobi.poi;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.StrUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFPictureData;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.core.io.FileSystemResource;
import org.springframework.http.ResponseEntity;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.*;

/**
 * @Description
 * @Author aoyebobi
 * @Date 2024-08-27
 */
@Slf4j
public class Demo {

    private static Map<String,Integer> questionTypeMap = new HashMap<>();
    private static Map<String,Integer> difficultMap = new HashMap<>();

    @Value("${file.img}")
    private String imgDir;
    static {
        questionTypeMap.put("单选题",0);
        questionTypeMap.put("多选题",1);
        questionTypeMap.put("不定项选择题",2);
        questionTypeMap.put("填空题",3);
        questionTypeMap.put("简答题",4);
        questionTypeMap.put("实操题",5);
        questionTypeMap.put("判断",6);
        questionTypeMap.put("其他",7);

        difficultMap.put("简单",0);
        difficultMap.put("普通",1);
        difficultMap.put("困难",2);
    }


    /**
     * 创建模板文件 返回一个可下载的文件
     * @param type
     * @return
     */
    public ResponseEntity<FileSystemResource> createTemplate(Integer type) {
        String[] data;
        if(type == 1){
            data = new String[]{"题干", "题目涉及资源", "题目类型", "选择题选项(','分隔)","关联知识点id(1,2)", "关联题库id",
                    "难易程度", "答案(单选:A;多选:A,B)","题目解析","题干|选项|答案图片数量(1,2,0)"};
        }else {
            data = new String[]{"题干", "题目涉及资源", "题目类型", "选择题选项(','分隔)", "关联知识点id(1,2)",
                    "关联题库id", "难易程度", "答案(单选:A;多选:A,B)", "题目解析"};
        }
        File file = new File("excel.xls");
        try {
            FileOutputStream outputStream = new FileOutputStream(file);
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet excelSheet = workbook.createSheet("excel");

            // 初始化表头
            HSSFRow sheetRow = excelSheet.createRow(0);
            // 创建一个字体对象
            Font font = workbook.createFont();
            // 设置字体颜色为红色
            font.setColor(IndexedColors.RED.getIndex());
            // 设置字体为粗体
            font.setBold(true);

            for (int column = 0; column < data.length; column++) {
                String text =  data[column];
                //自定义颜色
                RichTextString richText = workbook.getCreationHelper().createRichTextString(text);
                if(text.indexOf("*")>0){
                    int startIndex=0,  endIndex=0;
                    startIndex = text.indexOf("*");
                    endIndex = startIndex+1;
                    richText.applyFont(startIndex, endIndex, font);
                }

                sheetRow.createCell(column).setCellValue(richText);
            }

            // 创建单元格样式
            CellStyle style = workbook.createCellStyle();
            // 设置为文本格式，防止身份证号变成科学计数法
            DataFormat format = workbook.createDataFormat();
            style.setDataFormat(format.getFormat("@"));
            //对单独某一列进行样式赋值，第一个参数为列数，第二个参数为样式
            excelSheet.setDefaultColumnStyle(1, style);
            excelSheet.setDefaultColumnStyle(4, style);
            excelSheet.setDefaultColumnStyle(5, style);
            excelSheet.setDefaultColumnStyle(8, style);
            excelSheet.setDefaultColumnStyle(10,style);

            //定义题目类型
            String[] questionTypes = {"单选题","多选题","不定项选择题","填空题","简答题","实操题","判断","其他"};
            String[] difficultLevels = {"简单","普通","困难"};
            //设置题目类型下拉
            PoiUtils.addValidationToSheet(workbook, excelSheet, questionTypes, 'C', 1, 65534);
            //设置难度等级
            PoiUtils.addValidationToSheet(workbook, excelSheet, difficultLevels, 'G', 1, 65534);
            workbook.write(outputStream);
            outputStream.close();
            workbook.setSheetHidden(1,true);
            workbook.setSheetHidden(2,true);
        }catch (Exception e){
            log.error("xls模板创建失败");
        }
        return PoiUtils.export(file);
    }

    /**
     * 导入示例1：题目导入 纯文本
     * @param file
     */
    public void importQuestions(MultipartFile file) {
        try {
            String[] colNames = new String[]{"questionName", "mediaSource", "questionType", "questionOption",
                    "pointRelated", "bankRelated", "difficultLevel", "questionAnswer", "questionAnalyse"};
            String[] firstLineData = new String[]{"题干", "题目涉及资源", "题目类型", "选择题选项(','分隔)", "关联知识点id(1,2)", "关联题库id",
                    "难易程度", "答案(单选:A;多选:A,B)", "题目解析"};

            // 1.创建workbook对象，读取整个文档
            InputStream inputStream = file.getInputStream();
            POIFSFileSystem poifsFileSystem = new POIFSFileSystem(inputStream);
            HSSFWorkbook wb = new HSSFWorkbook(poifsFileSystem);
            // 2.读取页脚sheet
            HSSFSheet sheetAt = wb.getSheetAt(0);

            // 读取第一行，判断上传的文件格式是否正确
            HSSFRow firstRow = sheetAt.getRow(0);
            List<String> stringList = new ArrayList<>();
            short n = firstRow.getLastCellNum();
            if (firstLineData.length != n) {
                throw new Exception("文件格式不正确，请先下载模板文件再上传！");
            }
            for (int i = 0; i < n; i++) {
                if (!firstLineData[i].equals(firstRow.getCell(i)
                        .getStringCellValue())) {
                    throw new Exception("文件格式不正确，请先下载模板文件再上传！");
                }
            }

            List<Map<String, Object>> questionList = new ArrayList();
            //空文件返回错误信息
            if (sheetAt.getLastRowNum() == 0) {
                stringList.add("文件数据为空!");
            }
            // 3.循环读取每一行
            for (int i = 1; i <= sheetAt.getLastRowNum(); i++) {
                Map<String, Object> map = new LinkedHashMap<>();
                Row row = sheetAt.getRow(i);
                //读取题干
                String questionName = PoiUtils.getCellValueByCell(row.getCell(0)).trim();
                if (StrUtil.isEmpty(questionName)) {
                    stringList.add("第" + (i + 1) + "行" + "第1列的题干不能为空！");
                    continue;
                }
                map.put(colNames[0], questionName);
                map.put(colNames[1], PoiUtils.getCellValueByCell(row.getCell(1)).trim());
                //读取题目类型
                String questionType = PoiUtils.getCellValueByCell(row.getCell(2)).trim();
                if (StrUtil.isEmpty(questionType)) {
                    stringList.add("第" + (i + 1) + "行" + "第3列的题干不能为空！");
                    continue;
                }
                map.put(colNames[2], questionTypeMap.get(questionType));
                map.put(colNames[3], PoiUtils.getCellValueByCell(row.getCell(3)).trim());
                map.put(colNames[4], PoiUtils.getCellValueByCell(row.getCell(4)).trim());
                //读取题库id
                String relationId = PoiUtils.getCellValueByCell(row.getCell(5)).trim();
                if (StrUtil.isEmpty(relationId)) {
                    stringList.add("第" + (i + 1) + "行" + "第6列的题干不能为空！");
                    continue;
                }
                map.put(colNames[5], relationId);
                //读取难度
                String difficultLevel = PoiUtils.getCellValueByCell(row.getCell(6)).trim();
                if (StrUtil.isEmpty(difficultLevel)) {
                    stringList.add("第" + (i + 1) + "行" + "第7列的题干不能为空！");
                    continue;
                }
                map.put(colNames[6], difficultMap.get(difficultLevel));
                //读取答案
                String questionAnswer = PoiUtils.getCellValueByCell(row.getCell(7)).trim();
                if (StrUtil.isEmpty(questionAnswer)) {
                    stringList.add("第" + (i + 1) + "行" + "第8列的题干不能为空！");
                    continue;
                }
                map.put(colNames[7], questionAnswer);
                map.put(colNames[8], PoiUtils.getCellValueByCell(row.getCell(8)).trim());

                questionList.add(map);
            }

            //TODO 批量导入数据

        } catch (Exception e) {
            log.error("数据导入失败");
        }
    }


    /**
     * 导入示例2：题目导入 携带浮动图片
     * @param file
     */
    public void importQuestionsPics(MultipartFile file) {
        try {
            String[] colNames = new String[]{"questionName", "mediaSource", "questionType", "questionOption",
                    "pointRelated", "bankRelated", "difficultLevel", "questionAnswer", "questionAnalyse","pics"};
            String[] firstLineData = new String[]{"题干", "题目涉及资源", "题目类型", "选择题选项(','分隔)","关联知识点id(1,2)", "关联题库id",
                    "难易程度", "答案(单选:A;多选:A,B)","题目解析","题干|选项|答案图片数量(1,2,0)"};

            // 1.创建workbook对象，读取整个文档
            InputStream inputStream = file.getInputStream();
            POIFSFileSystem poifsFileSystem = new POIFSFileSystem(inputStream);
            HSSFWorkbook wb = new HSSFWorkbook(poifsFileSystem);
            List<HSSFPictureData> allPictures = wb.getAllPictures();
            List<File> pics = new ArrayList<>();
            File directory = new File(imgDir);
            //将图片暂存到本地
            if(CollUtil.isNotEmpty(allPictures)) {
                // 遍历所有图片
                int count = 1;
                if (!directory.exists()) {
                    directory.mkdirs();
                }
                for (HSSFPictureData picture : allPictures) {
                    // 生成文件路径
                    String fileName = "image_" + count + "." + picture.suggestFileExtension();
                    File picFile = new File(directory, fileName);

                    // 获取图片的字节数据
                    byte[] imageData = picture.getData();

                    // 将字节数据写入文件
                    try (FileOutputStream fos = new FileOutputStream(picFile)) {
                        fos.write(imageData);
                    }
                    pics.add(picFile);
                    count++;
                }
            }
            // 2.读取页脚sheet
            HSSFSheet sheetAt = wb.getSheetAt(0);

            // 读取第一行，判断上传的文件格式是否正确
            HSSFRow firstRow = sheetAt.getRow(0);
            List<String> stringList = new ArrayList<>();
            short n = firstRow.getLastCellNum();
            if (firstLineData.length != n) {
                throw new Exception("文件格式不正确，请先下载模板文件再上传！");
            }
            for (int i = 0; i < n; i++) {
                if (!firstLineData[i].equals(firstRow.getCell(i)
                        .getStringCellValue())) {
                    throw new Exception("文件格式不正确，请先下载模板文件再上传！");
                }
            }

            List<Map<String, Object>> questionList = new ArrayList();
            //空文件返回错误信息
            if(sheetAt.getLastRowNum() == 0){
                stringList.add("文件数据为空!");
            }
            // 3.循环读取每一行
            int currentPic = 1;
            for (int i = 1; i <= sheetAt.getLastRowNum(); i++) {
                Map<String, Object> map = new LinkedHashMap<>();
                Row row = sheetAt.getRow(i);
                //读取题干
                String questionName = PoiUtils.getCellValueByCell(row.getCell(0)).trim();
                if (StrUtil.isEmpty(questionName)) {
                    stringList.add("第" + (i + 1) + "行" + "第1列的题干不能为空！");
                    continue;
                }
                //将图片上传至minio 题干添加图片
                //获取当前行题干和答案的题目数量
                String picStr = PoiUtils.getCellValueByCell(row.getCell(9)).trim();
                String[] split = picStr.split(",");
                int questionNamePic = Integer.parseInt(split[0]);
                String questionNamePicUrls = uploadAndJoinPicUrl(currentPic, questionNamePic, pics);
                currentPic = currentPic + questionNamePic;
                map.put(colNames[0],questionName + questionNamePicUrls);
                map.put(colNames[1],PoiUtils.getCellValueByCell(row.getCell(1)).trim());
                //读取题目类型
                String questionType = PoiUtils.getCellValueByCell(row.getCell(2)).trim();
                if (StrUtil.isEmpty(questionType)) {
                    stringList.add("第" + (i + 1) + "行" + "第3列的题干不能为空！");
                    continue;
                }
                map.put(colNames[2],questionTypeMap.get(questionType));
                map.put(colNames[3],PoiUtils.getCellValueByCell(row.getCell(3)).trim());
                map.put(colNames[4],PoiUtils.getCellValueByCell(row.getCell(4)).trim());
                //读取题库id
                String relationId = PoiUtils.getCellValueByCell(row.getCell(5)).trim();
                if(StrUtil.isEmpty(relationId)){
                    stringList.add("第" + (i + 1) + "行" + "第6列的题干不能为空！");
                    continue;
                }
                map.put(colNames[5],relationId);
                //读取难度
                String difficultLevel = PoiUtils.getCellValueByCell(row.getCell(6)).trim();
                if(StrUtil.isEmpty(difficultLevel)){
                    stringList.add("第" + (i + 1) + "行" + "第7列的题干不能为空！");
                    continue;
                }
                map.put(colNames[6],difficultMap.get(difficultLevel));
                //读取答案
                int questionAnswerPic = Integer.parseInt(split[2]);
                String questionAnswerPicUrls = uploadAndJoinPicUrl(currentPic, questionAnswerPic, pics);
                currentPic += questionAnswerPic;
                String questionAnswer = PoiUtils.getCellValueByCell(row.getCell(7)).trim();
                if(StrUtil.isEmpty(questionAnswer)){
                    stringList.add("第" + (i + 1) + "行" + "第8列的题干不能为空！");
                    continue;
                }
                map.put(colNames[7],questionAnswer + questionAnswerPicUrls);
                map.put(colNames[8],PoiUtils.getCellValueByCell(row.getCell(8)).trim());
                questionList.add(map);
            }

            //TODO 批量导入数据
            FileUtil.del(directory);
        }catch (Exception e){
        }
    }

    /**
     * @param currentPic 当前图片序号
     * @param picNum 上传的图片数量
     * @return
     */
    private String uploadAndJoinPicUrl(Integer currentPic,Integer picNum,List<File> files){
        StringBuilder stringBuilder = new StringBuilder();
        for(int k = currentPic;k < currentPic+picNum ;k++ ){
//            MultipartFile multipartFile = FileUtils.getMultipartFile(files.get(k-1));
//            String url = minioUtil.uploadFileN(multipartFile, DEFAULT_BUCKET, TYPE_MAP.get("other").getFilePath());
//            stringBuilder.append("<img src=\"").append(url).append("\">");
        }
        return stringBuilder.toString();
    }
}
